package com.gerfmarquez.nymph.db.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME = "nymph_melody_creator.db";
	
	private static final int DATABASE_VERSION = 1;
	
	private static final String DATABASE_CREATE = "create table music_note (id_music_note integer primary key, note integer not null); " +
			"create table note_length (id_note_length integer primary key, length_nick integer not null, length_description text not null );" +
			"create table instrument (id_instrument integer primary key, instrument text not null);" +
			"create table song (id_song integer primary key autoincrement, name text not null, id_instrument integer not null);" +
			"create table song_note_length (id_song_note_length integer primary key autoincrement, id_song integer not null, id_music_note integer not null, id_note_length integer not null);" +
			"insert into music_note values (1, 1);" +
			"insert into music_note values (2, 2);" +
			"insert into music_note values (3, 3);" +
			"insert into music_note values (4, 4);" +
			"insert into music_note values (5, 5);" +
			"insert into music_note values (6, 6);" +
			"insert into music_note values (7, 7);" +
			"insert into music_note values (8, 8);" +
			"insert into music_note values (9, 9);" +
			"insert into music_note values (10, 10);" +
			"insert into music_note values (11, 11);" +
			"insert into music_note values (12, 12);" +
			"insert into note_length values (1, 1, \'Whole\');" +
			"insert into note_length values (2, 2, \'Half\');" +
			"insert into note_length values (3, 4, \'Quarter\');" +
			"insert into note_length values (4, 8, \'Eighth\');" +
			"insert into note_length values (5, 3, \'Third\');" +
			"insert into instrument values (1, \'Piano\');" +
			
			"insert into song values (1, \'Another Brick In The Wall\', 1);"+
			
			"insert into song_note_length values (null, 1, 3, 3);"+
			"insert into song_note_length values (null, 1, 5, 4);"+
			"insert into song_note_length values (null, 1, 6, 4);"+
			"insert into song_note_length values (null, 1, 0, 4);"+
			"insert into song_note_length values (null, 1, 5, 3);"+
			"insert into song_note_length values (null, 1, 0, 4);"+
			"insert into song_note_length values (null, 1, 3, 3);"+
			"insert into song_note_length values (null, 1, 5, 4);"+
			"insert into song_note_length values (null, 1, 6, 4);"+
			"insert into song_note_length values (null, 1, 0, 4);"+
			"insert into song_note_length values (null, 1, 5, 3);"+
			"insert into song_note_length values (null, 1, 0, 4);"+
			
			"insert into song values (2, \'Fur Elise\', 1);"+
			
			"insert into song_note_length values (null, 2, 5, 4);"+
			"insert into song_note_length values (null, 2, 4, 4);"+
			"insert into song_note_length values (null, 2, 5, 4);"+
			"insert into song_note_length values (null, 2, 4, 4);"+
			"insert into song_note_length values (null, 2, 5, 4);"+
			"insert into song_note_length values (null, 2, 12, 4);"+
			"insert into song_note_length values (null, 2, 3, 4);"+
			"insert into song_note_length values (null, 2, 1, 4);"+
			"insert into song_note_length values (null, 2, 10, 4);"+
			"insert into song_note_length values (null, 2, 5, 4);"+
			"insert into song_note_length values (null, 2, 10, 4);"+
			"insert into song_note_length values (null, 2, 1, 4);"+
			"insert into song_note_length values (null, 2, 5, 4);"+
			"insert into song_note_length values (null, 2, 10, 4);"+
			"insert into song_note_length values (null, 2, 12, 4);"+
			"insert into song_note_length values (null, 2, 5, 4);"+
			"insert into song_note_length values (null, 2, 9, 4);"+
			"insert into song_note_length values (null, 2, 5, 4);"+
			"insert into song_note_length values (null, 2, 9, 4);"+
			"insert into song_note_length values (null, 2, 12, 4);";
	
	private static final String TABLE_DELETE = "DROP TABLE IF EXISTS music_note;" +
			"DROP TABLE IF EXISTS note_length;" +
			"DROP TABLE IF EXISTS instrument;" +
			"DROP TABLE IF EXISTS song;" +
			"DROP TABLE IF EXISTS song_note_length;";
	
	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String []sql = utility(DATABASE_CREATE);
		for(int i = 0; i < sql.length; i++) {
			db.execSQL(sql[i]);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.w(DatabaseHelper.class.getName(),"Upgrading database from version "+oldVersion+" to "+ newVersion+" which will destroy all data");
		String []sql = utility(TABLE_DELETE);
		for(int i = 0; i < sql.length; i++) {
			db.execSQL(sql[i]);
		}
		onCreate(db);
	}
	private String[] utility(String toSplit) {
		return toSplit.split(";");
	}

}
